Перейти к основному содержимому

3.07. Сложные типы

В РАЗРАБОТКЕНЕ ДЛЯ НОВИЧКОВНЕ ОБЯЗАТЕЛЬНО

Разработчику Аналитику Тестировщику
Архитектору Инженеру

Сложные типы​

Разумеется, стандартными типами данных вроде текста, чисел и идентификаторов работа с SQL не ограничивается. Но важно понимать, что относится к «ванильному» SQL, который работает во всех СУБД, и что относится к конкретным возможностям, предоставляемым какими-то отдельными СУБД.

Самыми продвинутыми СУБД можно считать PostgreSQL и MSSQL, но давайте посмотрим, что можно в SQL еще сделать.

Массивы​

Массивами являются структуры данных, которые хранят несколько значений одного типа в одном поле (ячейке) таблицы. Они похожи на списки в языках программирования, и выглядят именно как [1, 2, 3] или ['яблоко', 'банан', 'апельсин'].

Не все БД поддерживают массивы, поэтому это расширение.

MySQL не поддерживает, SQLite не поддерживает, Oracle частично (там есть тип VARRAY и Nested Tables, но это сложные объектные типы, а не классические массивы).

PostgreSQL полноценно поддерживает массивы как встроенный тип данных:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    tags TEXT[],
    ratings INTEGER[]
);

INSERT INTO products (tags, ratings) VALUES
    (ARRAY['electronics', 'gadget'], ARRAY[5, 4, 5]),
    ('{book,fiction}', '{4,5}');

В данной команде мы создали массив tags с типом данных - массив текста (TEXT[]), а также массив ratings с типом данных - массив целых чисел (INTEGER[]). То есть, массив в данном случае обозначается квадратными скобками [].

Из интересных команд по работе с массивами можно отметить:

  • доступ к элементу: tags[1], ratings[2];
  • диапазон: tags[1:2];
  • длина массива: ARRAY_LENGTH(tags, 1);
  • развернуть в строки: UNNEST(tags);
  • конкатенация: tags || ARRAY['new'];
  • проверка наличия элемента: tag = ANY(tags).

На практике может пригодиться интересная команда UNNEST, которая превращает массив (ведь массив хранится в одной ячейке) в несколько ячеек. К примеру, если у нас tags имеет массив [‘electronics’, ‘gadget’], то команда UNNEST сделает из массива две строки - ‘electronics’ и ‘gadget’. То есть, «разбивает» массив на строки.

MSSQL не имеет встроенного типа ARRAY, поэтому массивы там эмулируют через XML, JSON, таблицы или строки с разделителями. Давайте теперь рассмотрим эти возможности.

Но MSSQL может разбивать строку на элементы через STRING_SPLIT(str, ',').

Хотя массивы удобны, они нарушают 1НФ (первую нормальную форму) реляционной модели — потому что в одной ячейке хранится несколько значений. Из-за них сложнее делать JOIN, индексация ограничивается, и нарушается принцип «одно значение - одна ячейка». Альтернативой массивам используют JSON.

JSON и JSONB.​

PostgreSQL имеет два типа: JSON и JSONB.

Первый хранит текст как есть, без индексации, а второй - бинарный, поддерживает индексы (что быстрее для поиска).

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    profile JSONB
);

INSERT INTO users (profile) VALUES
    ('{"name": "Alice", "hobbies": ["reading", "coding"]}');

Здесь создаётся JSONB. С ним можно выполнять операции:

  • -> — получить JSON-объект
  • ->> — получить текстовое значение
  • #> — путь по вложенным ключам
  • @> — содержит (для поиска)
  • ? — содержит ключ
  • jsonb_set(), jsonb_insert() — модификация
SELECT profile->>'name' FROM users;  -- "Alice"
SELECT * FROM users WHERE profile @> '{"hobbies": ["coding"]}';

MSSQL поддерживает JSON не как отдельный тип, но имеет встроенные функции для работы с текстовыми полями как с JSON. То есть, MSSQL не хранит JSON как отдельный тип — это NVARCHAR(MAX), и индексы нужно создавать вручную (через вычисляемые столбцы).

DECLARE @json NVARCHAR(MAX) = N'{"name": "Bob", "age": 30}';

SELECT 
    JSON_VALUE(@json, '$.name') AS name,
    JSON_QUERY(@json, '$.hobbies') AS hobbies;

Основные функции:

  • JSON_VALUE() — извлечь скаляр (строку, число)
  • JSON_QUERY() — извлечь объект/массив
  • ISJSON() — проверить валидность
  • OPENJSON() — развернуть JSON в таблицу
  • FOR JSON AUTO / PATH — формировать JSON из результата

XML​

В PostgreSQL есть поддержка XML, есть отдельный тип данных, и функции XMLPARSE, XMLSERIALIZE, XMLELEMENT, XMLATTRIBUTES, XPath через xpath().

MSSQL тоже поддерживает XML, имеет отдельный тип с полной валидацией, индексами, XQuery (язык запросов). Пример:

DECLARE @x XML = '<person name="Alice" age="25"/>';

SELECT @x.value('(/person/@name)[1]', 'VARCHAR(50)');
SELECT @x.query('/person');

Возможности:

  • value() — извлечь скаляр
  • query() — вернуть XML-фрагмент
  • nodes() — развернуть XML в строки (аналог UNNEST)
  • modify() — изменять XML (редактирование)

Словом, могут на практике возникать ситуации, когда нужно хранить результат, допустим, запроса или ответа в интеграциях. Когда одна система отправляет в другую некий запрос, и во вложениях будет XML/JSON или какой-то массив. И тут либо принимающая система парсит текст, читая, разбивая и распределяя по таблицам, либо просто закидывает вложение целиком в отдельный столбец в БД. Тогда допустим у нас будет в PostgreSQL отдельное поле, с которым можно работать.

Далее уже всё зависит от архитектурного решения - приложение может либо получать значение вложения целиком, а потом работать в коде (допустим парсинг в Java/C# соответственного XML/JSON/массива), либо использовать инструменты СУБД, но тогда можно упереться в возможности такой системы - для наибольшего функционала, конечно, лучше использовать PostgreSQL.